package jsyf.oa.dao.wreport;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

import jsyf.oa.entity.wreport.WReportCostEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;

/**
 * @author: @张巨波 @zhangjubo
 * @creat: on 2017/11/27.
 * @since: V1.0.0
 * @see jsyf.oa.web
 */
@Repository
public class WReportCostDao extends BaseDao {

    @Autowired
    WReportExpensesDao wReportExpensesDao;

    public List<JSONObject> getJSONListByPage1(String userId, String condition, Object[] params, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT ");
        sql.append(" MONEY AS money, ");
        sql.append(" REPORT_DATE AS reportDate, ");
        sql.append(" CREATE_TIME AS createTime,");
        //sql.append(" DATE_FORMAT(CREATE_TIME, '%H:%i %m-%d') AS createTime, ");
        sql.append(" CURRENT_AUDITOR_NAME AS currentAuditorName, ");
        sql.append(" T_WRT_COST.ID AS id, ");
        sql.append(" CREATE_USER_ID AS createUserId, ");
        sql.append(" AUDIT_FLAG AS auditFlag, ");
        sql.append(" INVOICE_FLAG AS invoiceFlag, ");
        sql.append(" CREATE_USER_NAME AS createUserName, ");
        sql.append(" COST_BIG_NAME AS costBigName, ");
        sql.append(" REMARK AS remark, ");
        sql.append(" COST_BIG_ID AS costBigId, ");
        sql.append(" W_ID AS wid, ");
        sql.append(" CURRENT_AUDITOR_ID AS currentAuditorId, ");
        sql.append(" COST_SMALL_ID AS costSmallId, ");
        sql.append(" COST_SMALL_NAME AS costSmallName,");
        sql.append(" T_WRT_COST_DETAIL.IMG AS img,");
        sql.append(" T_WRT_COST_DETAIL.PREDICT_MONEY AS predictMoney,");
        sql.append(" T_WRT_COST_DETAIL.MILEAGE AS mileage,");
        sql.append(" T_WRT_COST_DETAIL.LICENSE AS license,");
        sql.append(" T_WRT_COST_DETAIL.HOTEL_NAME AS hotelName,");
        sql.append(" T_WRT_COST_DETAIL.HOTEL_TEL AS hotelTel,");
        sql.append(" T_WRT_COST_DETAIL.RESIDENCE AS residence,");
        sql.append(" T_WRT_COST_DETAIL.HOST AS host,");
        sql.append(" T_WRT_COST_DETAIL.GUEST_NATURE AS guestNature,");
        sql.append(" T_WRT_COST_DETAIL.GUESTS AS guests,");
        sql.append(" T_WRT_COST_DETAIL.RESTAURANT_NAME AS restaurantName,");
        sql.append(" T_WRT_COST_DETAIL.RESTAURANT_TEL AS restaurantTel,");
        sql.append(" T_WRT_COST_DETAIL.ENTERTAINMENT_VENUE AS entertainmentVenue,");
        sql.append(" T_WRT_COST_DETAIL.PHONE AS phone,");
        sql.append(" T_WRT_COST_DETAIL.BEFORE_OFFICE AS beforeOffice,");
        sql.append(" T_WRT_COST_DETAIL.AFTER_OFFICE AS afterOffice,");
        sql.append(" T_WRT_COST_DETAIL.OFFICE_MATTERS AS officeMatters,");
        sql.append("CASE AUDIT_FLAG ");
        // 1 自己的草稿
        sql.append("  WHEN '-1' THEN 1");
        // 2 轮到自己审核的
        sql.append("  WHEN '0' THEN ");
        sql.append("  CASE CURRENT_AUDITOR_ID  ");
        sql.append("  WHEN '" + userId + "' THEN 1 ");
        sql.append("  ELSE 3 ");
        sql.append("  END ");
        sql.append("  ELSE CASE MONEY OR T_WRT_COST_DETAIL.IMG WHEN \"\" THEN 2 ");
        sql.append("  ELSE 3 END ");
        sql.append("  END AS sortNo");
        sql.append(" FROM ");
        sql.append("T_WRT_COST");
        sql.append(condition);
        sql.append("ORDER BY sortNo ASC,CREATE_TIME DESC");
        super.appendPageSql(sql, requestPage, pageSize);

        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), params);

        return jsonList;
    }

    @Override
    public Integer getPageCount(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT COUNT(1) AS count FROM " + iBaseEntity.getTableName());
        sql.append(condition);
        String count = super.queryForString(sql.toString(), params);
        return Integer.parseInt(count);
    }

    public Integer getPageCount1(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT COUNT(1) AS count FROM  v_wrt_cost2");
        sql.append(condition);
        String count = super.queryForString(sql.toString(), params);
        return Integer.parseInt(count);
    }

    public Integer add(WReportCostEntity model) {
        model.setId(super.generateKey());
        model.setCreateTime(super.getCurrentTime());
        return super.insert("T_WRT_COST", model.getJSONObject());
    }

    public Integer update(WReportCostEntity model) {
        return super.update("T_WRT_COST", model.getJSONObject(), model.getJSONObjectId());
    }

    public void remove(String reportDate, String createUserId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" AND AUDIT_FLAG is null ");
        sql.append(" AND CREATE_USER_ID= ?");
        sql.append(" AND LEFT(REPORT_DATE,10)= ?");
        super.removeByCondition(sql.toString(), new String[]{createUserId, reportDate});
    }

    public List<WReportCostEntity> getByCondition(String condition, Object[] params) {
        return this.getByPage(condition, params, 1, Integer.MAX_VALUE);
    }

    public Integer getUnDoCount(String userId) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<>();
        sql.append(" SELECT COUNT(1) AS count FROM " + iBaseEntity.getTableName() + " WHERE CURRENT_AUDITOR_ID = ? ");
        sqlArgs.add(userId);
        String count = super.queryForString(sql.toString(), sqlArgs.toArray());
        return Integer.parseInt(count);
    }

    public List<JSONObject> getCosterTop10ByCostName(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT \n");
        sql.append(" t_wrt_expenses.ID AS id, \n");
        sql.append(" t_wrt_expenses.`NAME` AS costName \n");
        sql.append(" FROM t_wrt_expenses \n");
        sql.append(" WHERE 1 = 1 \n");
        sql.append(condition);
        sql.append("LIMIT 0,10;");

        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), params);
        return jsonList;
    }

    public List<JSONObject> getCostSmallNameList(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT \n");
        sql.append(" t_wrt_expenses.ID AS id, \n");
        sql.append(" t_wrt_expenses.`NAME` AS costSmallName \n");
        sql.append(" FROM t_wrt_expenses \n");
        sql.append(" WHERE 1 = 1 \n");
        sql.append(condition);
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), params);
        return jsonList;
    }

    public List<WReportCostEntity> getByPage(String condition, Object[] params, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT \n");
        sql.append(" t_wrt_cost.ID AS id, \n");
        sql.append(" t_wrt_cost.W_ID AS wID, \n");
        sql.append(" t_wrt_cost.COST_BIG_NAME AS costBigName, \n");
        sql.append(" t_wrt_cost.COST_BIG_ID AS costBigId, \n");
        sql.append(" t_wrt_cost.COST_SMALL_NAME AS costSmallName, \n");
        sql.append(" t_wrt_cost.COST_SMALL_ID AS costSmallId, \n");
        sql.append(" t_wrt_cost.MONEY AS money, \n");
        sql.append(" t_wrt_cost.INVOICE_FLAG AS invoiceFlag, \n");
        sql.append(" t_wrt_cost.AUDIT_FLAG AS auditFlag, \n");
        sql.append("\tt_wrt_cost.REMARK AS remark \n");
        sql.append(" FROM T_WRT_COST \n");
        sql.append(" LEFT JOIN t_wrt_cost_detail ON t_wrt_cost.ID=t_wrt_cost_detail.FK_COST_ID \n");
        sql.append(" WHERE 1 = 1 \n");
        sql.append(condition);
        sql.append(" ORDER BY SORT_NO ");
        super.appendPageSql(sql, requestPage, pageSize);
        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), params);
        List<WReportCostEntity> list = new ArrayList<>();

        for (int i = 0; i < jsonList.size(); i++) {
            WReportCostEntity model = JSON.toJavaObject(jsonList.get(i), WReportCostEntity.class);
            if (model.getCostBigId() != null) {
                String costBigId = String.valueOf(model.getCostBigId());
                if (!StringUtils.isEmpty(costBigId)) {
                    List<JSONObject> listCostSmall = wReportExpensesDao.getByCondition("  AND expenses.PARENT_ID = ?  ", new String[]{costBigId});
                    model.setCostSmallNameList(listCostSmall);
                }
            }
            list.add(model);
        }
        return list;
    }


    public List<JSONObject> getByUserIdAndReport(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT \n");
        sql.append(" T_WRT_COST.ID AS id, \n");
        sql.append(" T_SYS_USER.NAME AS name, \n");
        sql.append(" T_SYS_USER.TEL AS tel, \n");
        sql.append(" T_WRT_MAIN.REPORT_DATE AS reportDate, \n");
        sql.append(" T_WRT_COST.COST_BIG_NAME AS costBigName, \n");
        sql.append(" T_WRT_COST.COST_SMALL_NAME AS costBigSmall, \n");
        sql.append(" T_WRT_COST.MONEY AS money, \n");
        sql.append(" T_WRT_COST.INVOICE_FLAG AS invoiceFlag, \n");
        sql.append(" T_WRT_COST.REMARK AS remark, \n");
        sql.append(" T_WRT_COST.START AS start \n");
        sql.append(" FROM \n");
        sql.append(" T_WRT_COST \n");
        sql.append(" LEFT JOIN T_WRT_MAIN ON T_WRT_COST.W_ID = T_WRT_MAIN.ID \n");
        sql.append(" LEFT JOIN T_SYS_USER ON T_WRT_MAIN.USER_ID = T_SYS_USER.ID \n");
        sql.append(" WHERE 1=1 \n");
        sql.append(condition);
        sql.append(" ORDER BY T_SYS_USER. NAME,T_WRT_MAIN.REPORT_DATE DESC ");
        return super.queryForJsonList(sql.toString(), params);
    }

    public List<JSONObject> getByDepartIdAndReport(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT \n");
        sql.append(" v_sys_user.name,  \n");
        sql.append(" v_sys_user.tel,  \n");
        sql.append(" T_WRT_MAIN.REPORT_DATE AS reportDate, \n");
        sql.append(" T_WRT_COST.COST_BIG_NAME AS costBigName, \n");
        sql.append(" T_WRT_COST.COST_SMALL_NAME AS costBigSmall, \n");
        sql.append(" T_WRT_COST.MONEY AS money, \n");
        sql.append(" T_WRT_COST.INVOICE_FLAG AS invoiceFlag, \n");
        sql.append(" T_WRT_COST.REMARK AS remark\n");
        sql.append(" FROM \n");
        sql.append(" T_WRT_COST \n");
        sql.append(" LEFT JOIN T_WRT_MAIN ON T_WRT_COST.W_ID = T_WRT_MAIN.ID \n");
        sql.append(" LEFT JOIN v_sys_user ON T_WRT_MAIN.USER_ID = v_sys_user.ID \n");
        sql.append(" WHERE 1=1 \n");
        sql.append(condition);
        sql.append(" ORDER BY v_sys_user.name,T_WRT_MAIN.REPORT_DATE ASC ");
        return super.queryForJsonList(sql.toString(), params);
    }

    public WReportCostDao() {
        this.iBaseEntity = new WReportCostEntity();
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, WReportCostEntity.class);
    }

    public WReportCostEntity getEntityById(String id) {
        return (WReportCostEntity) super.getIBaseEntityById(id);
    }

    public JSONObject getByDetail(Integer costSmallId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT \n");
        sql.append(" t2.`PHONE_NAME` AS costSmallName, \n");
        sql.append(" t2.ID AS costSmallId, \n");
        sql.append(" t1.`NAME` AS costBigName, \n");
        sql.append(" t1.ID AS costBigId \n");
        sql.append(" FROM \n");
        sql.append(" t_wrt_expenses t1, \n");
        sql.append(" t_wrt_expenses t2 \n");
        sql.append(" WHERE t1.ID = t2.PARENT_ID \n");
        // 查询条件
        sql.append(" AND t2.ID = ? \n");
        Object[] params = {costSmallId};
        // 查询结果
        return super.queryForJsonObject(sql.toString(), params);
    }

    public Integer copierReturn(String id, String copierReturnReason) {
        JSONObject obj = new JSONObject();
        obj.put("ID", id);
        obj.put("COPIER_RETURN_REASON", copierReturnReason);
        obj.put("AUDIT_FLAG", "2");
        obj.put("CURRENT_AUDITOR_ID", "");
        obj.put("CURRENT_AUDITOR_NAME", "");
        JSONObject objId = new JSONObject();
        objId.put("ID", id);
        return super.update("T_WRT_COST", obj, objId);
    }

    public List<JSONObject> getViewByPage(String condition, Object[] params, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT * FROM ");
        sql.append("V_WRT_COST2");
        sql.append(condition);

        super.appendPageSql(sql, requestPage, pageSize);

        List<JSONObject> jsonList = super.queryForJsonList(sql.toString(), params);

        return jsonList;
    }

    public List<JSONObject> getAllAuditList(String currentUserId, String condition, Object[] params, int requestPage, int pageSize) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT id,");
        sql.append(" type,companyId,money,urgentFlag,title,remark,auditFlag,createTime,createUserId,createUserName,currentAuditorName,currentAuditorId,img,flag,predictMoney,costSmallId,");
        sql.append("CASE auditFlag ");
        // 1 自己的草稿
        sql.append("    WHEN '-1' THEN 1");
        // 2 轮到自己审核的
        sql.append("    WHEN '0' THEN ");
        sql.append("    CASE currentAuditorId ");
        sql.append("      WHEN '" + currentUserId + "' THEN 1 ");
        sql.append("      ELSE 3 ");
        sql.append("    END ");
        sql.append("    WHEN '3' THEN ");
        sql.append("    CASE currentAuditorId ");
        sql.append("      WHEN '" + currentUserId + "' THEN 2 ");
        sql.append("      ELSE 3 ");
        sql.append("    END ");
        sql.append("    WHEN '1' THEN ");
        sql.append("    CASE flag WHEN '临时费用' THEN");
        sql.append("    CASE MONEY OR IMG WHEN \"\" THEN 1");
        sql.append("    ELSE 3 ");
        sql.append("    END ");
        sql.append(" ELSE 3");
        sql.append("    END ");
        sql.append("    WHEN '4' THEN 3 ");
        sql.append("    WHEN '2' THEN 3 ");
        sql.append("    WHEN '5' THEN 3 ");
        sql.append("    WHEN '6' THEN 3 ");
        sql.append("END AS sortNo ");
        sql.append(" FROM v_sys_audit_list ");
        sql.append(" WHERE 1 = 1 ");
        sql.append(condition);
        sql.append("ORDER BY sortNo,createTime desc");
        super.appendPageSql(sql, requestPage, pageSize);
        return super.queryForJsonList(sql.toString(), params);
    }

    public Integer getAllAuditCount(String condition, Object[] params) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT COUNT(1) AS count FROM v_sys_audit_list WHERE 1 = 1 ");
        sql.append(condition);
        String count = super.queryForString(sql.toString(), params);
        return Integer.parseInt(count);
    }

    public JSONObject getLastCost(String userId, String costType, String createTime) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tt_wrt_cost.ID as id,\n" +
                "\tt_wrt_cost.COST_SMALL_NAME as costSmallName,\n" +
                "\tt_wrt_cost.MONEY as money,\n" +
                "\tREPORT_DATE as reportDate,\n" +
                "\tt_wrt_cost_detail.MILEAGE as mileage\n" +
                "FROM\n" +
                "\tt_wrt_cost,\n" +
                "\tt_wrt_cost_detail\n" +
                "WHERE\n" +
                "\tt_wrt_cost.ID = t_wrt_cost_detail.FK_COST_ID\n" +
                "AND CREATE_USER_ID = ?\n" +
                "AND COST_SMALL_NAME LIKE '%加油费'\n" +
                "AND CREATE_TIME < ?\n" +
                "ORDER BY\n" +
                "\tCREATE_TIME DESC\n" +
                "LIMIT 1");
        Object[] params = {userId, createTime};
        return queryForJsonObject(sql.toString(), params);
    }
}
